IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spMessageTypeSelectAndInsertIfNotExist') AND type in (N'P', N'PC'))
	DROP PROCEDURE spMessageTypeSelectAndInsertIfNotExist
GO
-- =============================================
-- Select the MessageTypeID and MessageName if data already is avalable in DB.
-- Otherwise, try to insert new values to [MessageType] and [MessageTypeGroup] table.
-- The insertion is done base on the SourceID's value because with each SourceID has different  
-- logic to insert into these 2 tables.--
-- @return [MessageTypeID] and [MessageTitle] as a table, values can be NULL if not found
-- =============================================
CREATE PROCEDURE spMessageTypeSelectAndInsertIfNotExist
	@SourceId INT,
	@MessageTypeName NVARCHAR(250),
	@MessageGroupName NVARCHAR(250) = NULL
AS
BEGIN
	BEGIN TRAN T1
	
	DECLARE @MessageTypeId INT = NULL
	DECLARE @MessageTitle NVARCHAR(250) = NULL
	
	-- For HongKong markets
	IF @SourceId = 12
	BEGIN
		-- #section: process MessageGroup
		DECLARE @MessageGroupId INT
		-- check existing MessageGroup
		SELECT	TOP 1 @MessageGroupId = MessageGroupId 
		FROM	MessageType 
		WHERE	Name = @MessageGroupName 
				AND SourceId = @SourceId 
				AND MessageGroupTypeId IS NULL
			
		-- if it's not existing
		IF(LEN(@MessageGroupName) > 0 AND @MessageGroupId IS NULL)
		BEGIN
			DECLARE @mtGroupID INT
			SET @mtGroupID = ISNULL((SELECT (MAX(MessageTypeId)) + 1 FROM MessageType WHERE SourceId = @SourceId), 1)
			SET @MessageGroupId = ISNULL((SELECT (MAX(MessageGroupId)) + 1 FROM MessageType WHERE SourceId = @SourceId), 1)
		
			-- insert new MessageGroup
			INSERT INTO MessageType(MessageTypeId, Name, SourceId, MessageGroupId, MessageGroupTypeId)
			VALUES(@mtGroupID, @MessageGroupName, @SourceId, @MessageGroupId, NULL)
		END
		
		-- #section: process MessageType
		SELECT TOP 1 @MessageTypeId = MessageTypeId FROM MessageType WHERE Name = @MessageTypeName AND ISNULL(MessageGroupId, -1) = ISNULL(@MessageGroupId, -1) AND SourceId = @SourceId
		IF LEN(@MessageTypeName) > 0 AND @MessageTypeId IS NULL
		BEGIN	
			SET @MessageTypeId = ISNULL((SELECT (MAX(MessageTypeId)) + 1 FROM MessageType WHERE SourceId = @SourceId), 1)
		
			-- insert new MessageType
			INSERT INTO MessageType(MessageTypeId, Name, SourceId, MessageGroupId, MessageGroupTypeId)
			VALUES (@MessageTypeId, @MessageTypeName, @SourceId, @MessageGroupId, @MessageGroupId)
		END
	END

	-- For Custome
	IF @SourceId <> 12
	BEGIN
		SELECT TOP 1 @MessageTypeId = MessageTypeId FROM MessageType WHERE Name = @MessageTypeName AND SourceId = @SourceId
		SELECT TOP 1 @MessageTitle = [Description] FROM dbo.MessageTypeGroup WHERE SourceID = @SourceId AND MessageTypeId = @MessageTypeId
		IF @MessageTitle IS NULL
		BEGIN
			-- insert new message type 'Other' if it's not existing
			DECLARE @mtOtherID INT
			SELECT TOP 1 @mtOtherID = MessageTypeId FROM MessageType WHERE SourceId = @SourceId AND Name = 'Other'
			
			IF @mtOtherID IS NULL
				BEGIN
					SELECT @mtOtherID = MAX(MessageTypeId) FROM MessageType WHERE SourceId = @SourceId
					SET @mtOtherID = ISNULL(@mtOtherID, 0) + 1
					INSERT INTO MessageType VALUES(@mtOtherID, NULL,NULL, 'Other', NULL, GETDATE(), GETDATE(), 1,@SourceId)
				END
			
			SELECT @MessageTypeId = MAX(MessageTypeId) + 1 FROM MessageType WHERE SourceId = @SourceId
			INSERT INTO MessageType VALUES(@MessageTypeId,NULL,NULL, @MessageTypeName, NULL, GETDATE(), GETDATE(), 1,@SourceId)
			INSERT INTO MessageTypeGroup VALUES (@MessageTypeId, @mtOtherID, @SourceId, @MessageTypeName, GETDATE(),GETDATE(),1,0)
			
			SET @MessageTitle = @MessageTypeName
		END
	END
	
	IF (@@ERROR <> 0)
		BEGIN
			ROLLBACK TRAN T1
			SET @MessageTypeId = NULL
			SET @MessageTitle = NULL
		END
	ELSE
		COMMIT TRAN T1

	SELECT @MessageTypeId AS 'MessageTypeId', @MessageTitle as 'MessageTypeName'
END

